<?php
class Advertiseseff_model extends CRM_Model{
	
	public function __construct(){
		parent::__construct();
	}
	
	public function GetReportDS(){
		$this->db->select('_advertisessources.source_name as source_name, 
							_advertisestypes.type_name as source_type, 
							_cities.city_name as cityName,
							_filials.name as filialName,
							count(DISTINCT(_calls_rows.rid)) as callsQuan, 
							count(DISTINCT(_demands_headers.rid)) as demandsQuan,
							ROUND(sum(_outgoing_incoming.summ_value), 0) as advSum, 
							ROUND(sum(DISTINCT((_tours.summ_tour - _tours.summ_tour*(_demands_headers.discount_per/100) - _demands_headers.discount_fix)*_demands_headers.curr_cource*_demands_headers.touroperator_koeff - _tours.order_summ) ), 0) as doxod');
		$this->db->from('_advertisessources');
		$this->db->join('_advertisestypes', '_advertisestypes.rid = _advertisessources._advertisestypes_rid');
		$this->db->join('_calls_rows', '_calls_rows._advertisessources_rid= _advertisessources.rid', 'LEFT');
		$this->db->join('_demands_headers', '_demands_headers._advertisessources_rid= _advertisessources.rid', 'LEFT');
		$this->db->join('_tours', '_tours.rid =  _demands_headers._tours_rid', 'LEFT');
		$this->db->join('_advertises_rows', '_advertises_rows._advertisessources_rid = _advertisessources.rid', 'LEFT');
		$this->db->join('_advertises_headers', '_advertises_rows._advertises_headers_rid = _advertises_headers.rid', 'LEFT');
		$this->db->join('_outgoing_incoming', '_outgoing_incoming._documents_rid = _advertises_headers._documents_rid', 'LEFT');
		$this->db->join('_filials', '_outgoing_incoming._filials_rid = _filials.rid', 'LEFT');
		$this->db->join('_cities', '_advertises_rows._cities_rid = _cities.rid', 'LEFT');
		$this->db->where("(_demands_headers.date_doc>='{$this->toMySQLDate($_POST['bdate'])}' AND _demands_headers.date_doc<='{$this->toMySQLDate($_POST['edate'])}') OR 
							(_advertises_headers.date_doc>='{$this->toMySQLDate($_POST['bdate'])}' AND _advertises_headers.date_doc<='{$this->toMySQLDate($_POST['edate'])}')");
		if($_POST['city']!=' ') $this->db->where(array('_cities.rid'=>$_POST['city']));
		$this->db->groupby('_advertisestypes.rid, _advertisessources.rid, _cities.rid, _filials.rid');
		$this->db->having('callsQuan+demandsQUAN > 0');
		$this->db->orderby('_cities.city_name, _filials.name, _advertisessources.source_name');
		$query = $this->db->get();
		if($query->num_rows()>0) return $query->result();
		return array();
	}
	
	public function to_Excel(){
		$ds = $this->GetReportDS();
		$ds_C = $ds;
		$ds_F = $ds;
		//var_dump($ds);die();
		$xls =& new Spreadsheet_Excel_Writer(); 
		$xls->send("advertise.xls"); 
		$sheet =& $xls->addWorksheet('advertise');
		$sheet->SetColumn(0, 9, 16);
		// { Формат заголовка
		$titleFormat =& $xls->addFormat();
		$titleFormat->setFontFamily('Helvetica');
		$titleFormat->setBold();
		$titleFormat->setSize('11');
		// } Формат заголовка
		
		$headerFormat =& $xls->addFormat();
		$headerFormat->setFontFamily('Helvetica');
		$headerFormat->setSize('10');
		$headerFormat->setBorder(1);
		$headerFormat->setBorderColor('black');
		$headerFormat->setBgColor('silver');

		$cityFormat =& $xls->addFormat();
		$cityFormat->setFontFamily('Helvetica');
		$cityFormat->setSize('10');
		$cityFormat->setBold();
		$cityFormat->setBorder(1);
		$cityFormat->setBorderColor('black');
		$cityFormat->setBgColor('yellow');

		
		$filialFormat =& $xls->addFormat();
		$filialFormat->setFontFamily('Helvetica');
		$filialFormat->setSize('10');
		$filialFormat->setBold();
		
		$filialAllFormat =& $xls->addFormat();
		$filialAllFormat->setFontFamily('Helvetica');
		$filialAllFormat->setSize('10');
		$filialAllFormat->setBold();

		$cityAllFormat =& $xls->addFormat();
		$cityAllFormat->setFontFamily('Helvetica');
		$cityAllFormat->setSize('10');
		$cityAllFormat->setBold();
		
		//$filialFormat->setBgColor('yellow');
		
		$textFormat =& $xls->addFormat();
		$textFormat->setFontFamily('Helvetica');
		$textFormat->setSize('10');
		
		$titleText = iconv('UTF-8', 'Windows-1251', "Отчет по эффективности рекламных площадок за {$_POST['bdate']} - {$_POST['edate']}");
		$sheet->write(0,0,$titleText,$titleFormat);
		$headerArr = array('Тип', 'Носитель', 'Стоимость', 'Контакты', 'Продажи', 'Прибыль', 'Эффективность', 'Доход с рекламы');
		$i = 0;
		foreach($headerArr as $item){
			
			$sheet->write(1,$i,iconv('UTF-8', 'Windows-1251', $item),$headerFormat);
			$i++;
		}
		$curr_city = null;
		$curr_filial = null;
		$SUMM_COST = 0;
		$SUMM_CONT = 0;
		$SUMM_DMNDS = 0;
		$SUMM_DOXOD = 0;
		$SUMM_EFF = 0;
		$SUMM_AD = 0;
		
		$curr_row = 3;
		foreach($ds as $row){
			if(!$row->cityName) continue;
			if($curr_filial && $curr_filial != $row->filialName){
				$SUMM_COST = 0;$SUMM_CONT = 0;$SUMM_DMNDS = 0;$SUMM_DOXOD = 0;$SUMM_EFF = 0;$SUMM_AD = 0;
				foreach($ds_F as $row_F){
					if($row_F->filialName == $curr_filial){
						$SUMM_COST += $row_F->advSum;
						$SUMM_CONT += $row_F->callsQuan;
						$SUMM_DMNDS += $row_F->demandsQuan;
						$SUMM_DOXOD += $row_F->doxod;
						$SUMM_EFF += ($row_F->advSum)?$row_F->doxod/$row_F->advSum:0;
						$SUMM_AD += ($row_F->doxod-$row_F->advSum);
					}
				}
				$curr_row++;
				$sheet->write($curr_row, 2,$SUMM_COST,$filialAllFormat);
				$sheet->write($curr_row, 3,$SUMM_CONT,$filialAllFormat);
				$sheet->write($curr_row, 4,$SUMM_DMNDS,$filialAllFormat);
				$sheet->write($curr_row, 5,$SUMM_DOXOD,$filialAllFormat);
				$sheet->write($curr_row, 6,$SUMM_EFF,$filialAllFormat);
				$sheet->write($curr_row, 7,$SUMM_AD,$filialAllFormat);
				$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего по филиалу'), $filialAllFormat);
			}
			if($curr_city != $row->cityName) {
				if($row->cityName){
					if($curr_city){
						$curr_row++;
						$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего по '.$curr_city), $filialAllFormat);	
						
						
			/*************************************************************************/
		// Показываем по городу в целом
		$nDs = array();
		$summNames = array();
		$summTypes = array();
		foreach($ds as $row222){
			if($row222->cityName != $curr_city) continue;
			$nDs[$row222->source_type][$row222->source_name] = $row222;
			$summTypes[$row222->source_type]['SUMM_COST'] = (isset($summTypes[$row222->source_type]['SUMM_COST']))?$summTypes[$row222->source_type]['SUMM_COST']+$row222->advSum:$row222->advSum;
			$summTypes[$row222->source_type]['SUMM_CONT'] = (isset($summTypes[$row222->source_type]['SUMM_CONT']))?$summTypes[$row222->source_type]['SUMM_CONT']+$row222->callsQuan:$row222->callsQuan;
			$summTypes[$row222->source_type]['SUMM_DMNDS'] = (isset($summTypes[$row222->source_type]['SUMM_DMNDS']))?$summTypes[$row222->source_type]['SUMM_DMNDS']+$row222->demandsQuan:$row222->demandsQuan;
			$summTypes[$row222->source_type]['SUMM_DOXOD'] = (isset($summTypes[$row222->source_type]['SUMM_DOXOD']))?$summTypes[$row222->source_type]['SUMM_DOXOD']+$row222->doxod:$row222->doxod;
			$eff = ($row222->advSum)?$row222->doxod/$row222->advSum:0;
			$summTypes[$row222->source_type]['SUMM_EFF'] = (isset($summTypes[$row222->source_type]['SUMM_EFF']))?$summTypes[$row222->source_type]['SUMM_EFF']+$eff:$eff;
			$summTypes[$row222->source_type]['SUMM_AD'] = (isset($summTypes[$row222->source_type]['SUMM_AD']))?$summTypes[$row222->source_type]['SUMM_AD']+($row222->doxod-$row222->advSum):($row222->doxod-$row222->advSum);
			
			$summNames[$row222->source_name]['SUMM_COST'] = (isset($summNames[$row222->source_name]['SUMM_COST']))?$summNames[$row222->source_name]['SUMM_COST']+$row222->advSum:$row222->advSum;
			$summNames[$row222->source_name]['SUMM_CONT'] = (isset($summNames[$row222->source_name]['SUMM_CONT']))?$summNames[$row222->source_name]['SUMM_CONT']+$row222->callsQuan:$row222->callsQuan;
			$summNames[$row222->source_name]['SUMM_DMNDS'] = (isset($summNames[$row222->source_name]['SUMM_DMNDS']))?$summNames[$row222->source_name]['SUMM_DMNDS']+$row222->demandsQuan:$row222->demandsQuan;
			$summNames[$row222->source_name]['SUMM_DOXOD'] = (isset($summNames[$row222->source_name]['SUMM_DOXOD']))?$summNames[$row222->source_name]['SUMM_DOXOD']+$row222->doxod:$row222->doxod;
			$eff = ($row222->advSum)?$row222->doxod/$row222->advSum:0;
			$summNames[$row222->source_name]['SUMM_EFF'] = (isset($summNames[$row222->source_name]['SUMM_EFF']))?$summNames[$row222->source_name]['SUMM_EFF']+$eff:$eff;
			$summNames[$row222->source_name]['SUMM_AD'] = (isset($summNames[$row222->source_name]['SUMM_AD']))?$summNames[$row222->source_name]['SUMM_AD']+($row222->doxod-$row222->advSum):($row222->doxod-$row222->advSum);
			
		}
		
		$curr_type = null;
		$sheet->mergeCells($curr_row, 0, $curr_row, 7);	
		//$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'По сети в целом'),$cityFormat);
		$curr_row++;
		foreach($nDs as $key=>$rows){
			foreach($rows as $row222){
				if($curr_type != $row222->source_type) {
					$curr_type = $row222->source_type;
					$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', $row222->source_type), $textFormat);
					
					$sheet->write($curr_row, 2,$summTypes[$row222->source_type]['SUMM_COST'],$textFormat);
					$sheet->write($curr_row, 3,$summTypes[$row222->source_type]['SUMM_CONT'],$textFormat);
					$sheet->write($curr_row, 4,$summTypes[$row222->source_type]['SUMM_DMNDS'],$textFormat);
					$sheet->write($curr_row, 5,$summTypes[$row222->source_type]['SUMM_DOXOD'],$textFormat);
					$sheet->write($curr_row, 6,$summTypes[$row222->source_type]['SUMM_EFF'],$textFormat);
					$sheet->write($curr_row, 7,$summTypes[$row222->source_type]['SUMM_AD'],$textFormat);
					
					$curr_row++;
				}
				$sheet->write($curr_row, 1,iconv('UTF-8', 'Windows-1251', $row222->source_name),$textFormat);
				$sheet->write($curr_row, 2,$summNames[$row222->source_name]['SUMM_COST'],$textFormat);
				$sheet->write($curr_row, 3,$summNames[$row222->source_name]['SUMM_CONT'],$textFormat);
				$sheet->write($curr_row, 4,$summNames[$row222->source_name]['SUMM_DMNDS'],$textFormat);
				$sheet->write($curr_row, 5,$summNames[$row222->source_name]['SUMM_DOXOD'],$textFormat);
				$sheet->write($curr_row, 6,$summNames[$row222->source_name]['SUMM_EFF'],$textFormat);
				$sheet->write($curr_row, 7,$summNames[$row222->source_name]['SUMM_AD'],$textFormat);
								
				$curr_row++;
			}
		}
			
			/*************************************************************************/
						
						
						
						
						$SUMM_COST = 0;$SUMM_CONT = 0;$SUMM_DMNDS = 0;$SUMM_DOXOD = 0;$SUMM_EFF = 0;$SUMM_AD = 0;
						foreach($ds_C as $row_C){
							if($row_C->cityName == $curr_city){
								$SUMM_COST += $row_C->advSum;
								$SUMM_CONT += $row_C->callsQuan;
								$SUMM_DMNDS += $row_C->demandsQuan;
								$SUMM_DOXOD += $row_C->doxod;
								$SUMM_EFF += ($row_C->advSum)?$row_C->doxod/$row_C->advSum:0;
								$SUMM_AD += ($row_C->doxod-$row_C->advSum);
							}
						}
				
						$curr_row++;
						$sheet->write($curr_row, 2,$SUMM_COST,$cityAllFormat);
						$sheet->write($curr_row, 3,$SUMM_CONT,$cityAllFormat);
						$sheet->write($curr_row, 4,$SUMM_DMNDS,$cityAllFormat);
						$sheet->write($curr_row, 5,$SUMM_DOXOD,$cityAllFormat);
						$sheet->write($curr_row, 6,$SUMM_EFF,$cityAllFormat);
						$sheet->write($curr_row, 7,$SUMM_AD,$cityAllFormat);
						//$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего по '.$curr_city), $cityAllFormat);
						$curr_row +=2;
					}
					$sheet->mergeCells($curr_row, 0, $curr_row, 7);	
					$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', $row->cityName),$cityFormat);
					$curr_row ++;
				}
				$curr_city = $row->cityName;
			}
			
			if($curr_filial != $row->filialName) {
				if($row->filialName){
					$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', $row->filialName),$filialFormat);
					$curr_row++;
					
				}
				$curr_filial = $row->filialName;
			}
			$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', $row->source_type),$textFormat);
			$sheet->write($curr_row, 1,iconv('UTF-8', 'Windows-1251', $row->source_name),$textFormat);
			$sheet->write($curr_row, 2,iconv('UTF-8', 'Windows-1251', $row->advSum),$textFormat);
			$sheet->write($curr_row, 3,iconv('UTF-8', 'Windows-1251', $row->callsQuan),$textFormat);
			$sheet->write($curr_row, 4,iconv('UTF-8', 'Windows-1251', $row->demandsQuan),$textFormat);
			$sheet->write($curr_row, 5,iconv('UTF-8', 'Windows-1251', $row->doxod),$textFormat);
			$sheet->write($curr_row, 6,($row->advSum)?$row->doxod/$row->advSum:'',$textFormat);
			$sheet->write($curr_row, 7,$row->doxod-$row->advSum,$textFormat);
			$curr_row++;
		}
		
		if($curr_filial){
			$SUMM_COST = 0;$SUMM_CONT = 0;$SUMM_DMNDS = 0;$SUMM_DOXOD = 0;$SUMM_EFF = 0;$SUMM_AD = 0;
			foreach($ds_F as $row_F){
				if($row_F->filialName == $curr_filial){
					$SUMM_COST += $row_F->advSum;
					$SUMM_CONT += $row_F->callsQuan;
					$SUMM_DMNDS += $row_F->demandsQuan;
					$SUMM_DOXOD += $row_F->doxod;
					$SUMM_EFF += ($row_F->advSum)?$row_F->doxod/$row_F->advSum:0;
					$SUMM_AD += ($row_F->doxod-$row_F->advSum);
				}
			}
			$curr_row++;
			$sheet->write($curr_row, 2,$SUMM_COST,$filialAllFormat);
			$sheet->write($curr_row, 3,$SUMM_CONT,$filialAllFormat);
			$sheet->write($curr_row, 4,$SUMM_DMNDS,$filialAllFormat);
			$sheet->write($curr_row, 5,$SUMM_DOXOD,$filialAllFormat);
			$sheet->write($curr_row, 6,$SUMM_EFF,$filialAllFormat);
			$sheet->write($curr_row, 7,$SUMM_AD,$filialAllFormat);
			$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего по филиалу'), $filialAllFormat);
		}
		
		if($curr_city){
			$curr_row++;
			$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего по '.$curr_city), $filialAllFormat);
			/*************************************************************************/
		// Показываем по городу в целом
		$nDs = array();
		$summNames = array();
		$summTypes = array();
		foreach($ds as $row222){
			if($row222->cityName != $curr_city) continue;
			$nDs[$row222->source_type][$row222->source_name] = $row222;
			$summTypes[$row222->source_type]['SUMM_COST'] = (isset($summTypes[$row222->source_type]['SUMM_COST']))?$summTypes[$row222->source_type]['SUMM_COST']+$row222->advSum:$row222->advSum;
			$summTypes[$row222->source_type]['SUMM_CONT'] = (isset($summTypes[$row222->source_type]['SUMM_CONT']))?$summTypes[$row222->source_type]['SUMM_CONT']+$row222->callsQuan:$row222->callsQuan;
			$summTypes[$row222->source_type]['SUMM_DMNDS'] = (isset($summTypes[$row222->source_type]['SUMM_DMNDS']))?$summTypes[$row222->source_type]['SUMM_DMNDS']+$row222->demandsQuan:$row222->demandsQuan;
			$summTypes[$row222->source_type]['SUMM_DOXOD'] = (isset($summTypes[$row222->source_type]['SUMM_DOXOD']))?$summTypes[$row222->source_type]['SUMM_DOXOD']+$row222->doxod:$row222->doxod;
			$eff = ($row222->advSum)?$row222->doxod/$row222->advSum:0;
			$summTypes[$row222->source_type]['SUMM_EFF'] = (isset($summTypes[$row222->source_type]['SUMM_EFF']))?$summTypes[$row222->source_type]['SUMM_EFF']+$eff:$eff;
			$summTypes[$row222->source_type]['SUMM_AD'] = (isset($summTypes[$row222->source_type]['SUMM_AD']))?$summTypes[$row222->source_type]['SUMM_AD']+($row222->doxod-$row222->advSum):($row222->doxod-$row222->advSum);
			
			$summNames[$row222->source_name]['SUMM_COST'] = (isset($summNames[$row222->source_name]['SUMM_COST']))?$summNames[$row222->source_name]['SUMM_COST']+$row222->advSum:$row222->advSum;
			$summNames[$row222->source_name]['SUMM_CONT'] = (isset($summNames[$row222->source_name]['SUMM_CONT']))?$summNames[$row222->source_name]['SUMM_CONT']+$row222->callsQuan:$row222->callsQuan;
			$summNames[$row222->source_name]['SUMM_DMNDS'] = (isset($summNames[$row222->source_name]['SUMM_DMNDS']))?$summNames[$row222->source_name]['SUMM_DMNDS']+$row222->demandsQuan:$row222->demandsQuan;
			$summNames[$row222->source_name]['SUMM_DOXOD'] = (isset($summNames[$row222->source_name]['SUMM_DOXOD']))?$summNames[$row222->source_name]['SUMM_DOXOD']+$row222->doxod:$row222->doxod;
			$eff = ($row222->advSum)?$row222->doxod/$row222->advSum:0;
			$summNames[$row222->source_name]['SUMM_EFF'] = (isset($summNames[$row222->source_name]['SUMM_EFF']))?$summNames[$row222->source_name]['SUMM_EFF']+$eff:$eff;
			$summNames[$row222->source_name]['SUMM_AD'] = (isset($summNames[$row222->source_name]['SUMM_AD']))?$summNames[$row222->source_name]['SUMM_AD']+($row222->doxod-$row222->advSum):($row222->doxod-$row222->advSum);
			
		}
		
		$curr_type = null;
		$sheet->mergeCells($curr_row, 0, $curr_row, 7);	
		//$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'По сети в целом'),$cityFormat);
		$curr_row++;
		foreach($nDs as $key=>$rows){
			foreach($rows as $row222){
				if($curr_type != $row222->source_type) {
					$curr_type = $row222->source_type;
					$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', $row222->source_type), $textFormat);
					
					$sheet->write($curr_row, 2,$summTypes[$row222->source_type]['SUMM_COST'],$textFormat);
					$sheet->write($curr_row, 3,$summTypes[$row222->source_type]['SUMM_CONT'],$textFormat);
					$sheet->write($curr_row, 4,$summTypes[$row222->source_type]['SUMM_DMNDS'],$textFormat);
					$sheet->write($curr_row, 5,$summTypes[$row222->source_type]['SUMM_DOXOD'],$textFormat);
					$sheet->write($curr_row, 6,$summTypes[$row222->source_type]['SUMM_EFF'],$textFormat);
					$sheet->write($curr_row, 7,$summTypes[$row222->source_type]['SUMM_AD'],$textFormat);
					
					$curr_row++;
				}
				$sheet->write($curr_row, 1,iconv('UTF-8', 'Windows-1251', $row222->source_name),$textFormat);
				$sheet->write($curr_row, 2,$summNames[$row222->source_name]['SUMM_COST'],$textFormat);
				$sheet->write($curr_row, 3,$summNames[$row222->source_name]['SUMM_CONT'],$textFormat);
				$sheet->write($curr_row, 4,$summNames[$row222->source_name]['SUMM_DMNDS'],$textFormat);
				$sheet->write($curr_row, 5,$summNames[$row222->source_name]['SUMM_DOXOD'],$textFormat);
				$sheet->write($curr_row, 6,$summNames[$row222->source_name]['SUMM_EFF'],$textFormat);
				$sheet->write($curr_row, 7,$summNames[$row222->source_name]['SUMM_AD'],$textFormat);
								
				$curr_row++;
			}
		}
			
			/*************************************************************************/
			
			
			
			$SUMM_COST = 0;$SUMM_CONT = 0;$SUMM_DMNDS = 0;$SUMM_DOXOD = 0;$SUMM_EFF = 0;$SUMM_AD = 0;
			foreach($ds_C as $row_C){
				if($row_C->cityName == $curr_city){
					$SUMM_COST += $row_C->advSum;
					$SUMM_CONT += $row_C->callsQuan;
					$SUMM_DMNDS += $row_C->demandsQuan;
					$SUMM_DOXOD += $row_C->doxod;
					$SUMM_EFF += ($row_C->advSum)?$row_C->doxod/$row_C->advSum:0;
					$SUMM_AD += ($row_C->doxod-$row_C->advSum);
				}
			}
			$curr_row++;
			$sheet->write($curr_row, 2,$SUMM_COST,$cityAllFormat);
			$sheet->write($curr_row, 3,$SUMM_CONT,$cityAllFormat);
			$sheet->write($curr_row, 4,$SUMM_DMNDS,$cityAllFormat);
			$sheet->write($curr_row, 5,$SUMM_DOXOD,$cityAllFormat);
			$sheet->write($curr_row, 6,$SUMM_EFF,$cityAllFormat);
			$sheet->write($curr_row, 7,$SUMM_AD,$cityAllFormat);
			//$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего по '.$curr_city), $cityAllFormat);
			$curr_row +=2;
		}
		
		// Показываем по сети в целом
		$nDs = array();
		$summNames = array();
		$summTypes = array();
		foreach($ds as $row){
			$nDs[$row->source_type][$row->source_name] = $row;
			$summTypes[$row->source_type]['SUMM_COST'] = (isset($summTypes[$row->source_type]['SUMM_COST']))?$summTypes[$row->source_type]['SUMM_COST']+$row->advSum:$row->advSum;
			$summTypes[$row->source_type]['SUMM_CONT'] = (isset($summTypes[$row->source_type]['SUMM_CONT']))?$summTypes[$row->source_type]['SUMM_CONT']+$row->callsQuan:$row->callsQuan;
			$summTypes[$row->source_type]['SUMM_DMNDS'] = (isset($summTypes[$row->source_type]['SUMM_DMNDS']))?$summTypes[$row->source_type]['SUMM_DMNDS']+$row->demandsQuan:$row->demandsQuan;
			$summTypes[$row->source_type]['SUMM_DOXOD'] = (isset($summTypes[$row->source_type]['SUMM_DOXOD']))?$summTypes[$row->source_type]['SUMM_DOXOD']+$row->doxod:$row->doxod;
			$eff = ($row->advSum)?$row->doxod/$row->advSum:0;
			$summTypes[$row->source_type]['SUMM_EFF'] = (isset($summTypes[$row->source_type]['SUMM_EFF']))?$summTypes[$row->source_type]['SUMM_EFF']+$eff:$eff;
			$summTypes[$row->source_type]['SUMM_AD'] = (isset($summTypes[$row->source_type]['SUMM_AD']))?$summTypes[$row->source_type]['SUMM_AD']+($row->doxod-$row->advSum):($row->doxod-$row->advSum);
			
			$summNames[$row->source_name]['SUMM_COST'] = (isset($summNames[$row->source_name]['SUMM_COST']))?$summNames[$row->source_name]['SUMM_COST']+$row->advSum:$row->advSum;
			$summNames[$row->source_name]['SUMM_CONT'] = (isset($summNames[$row->source_name]['SUMM_CONT']))?$summNames[$row->source_name]['SUMM_CONT']+$row->callsQuan:$row->callsQuan;
			$summNames[$row->source_name]['SUMM_DMNDS'] = (isset($summNames[$row->source_name]['SUMM_DMNDS']))?$summNames[$row->source_name]['SUMM_DMNDS']+$row->demandsQuan:$row->demandsQuan;
			$summNames[$row->source_name]['SUMM_DOXOD'] = (isset($summNames[$row->source_name]['SUMM_DOXOD']))?$summNames[$row->source_name]['SUMM_DOXOD']+$row->doxod:$row->doxod;
			$eff = ($row->advSum)?$row->doxod/$row->advSum:0;
			$summNames[$row->source_name]['SUMM_EFF'] = (isset($summNames[$row->source_name]['SUMM_EFF']))?$summNames[$row->source_name]['SUMM_EFF']+$eff:$eff;
			$summNames[$row->source_name]['SUMM_AD'] = (isset($summNames[$row->source_name]['SUMM_AD']))?$summNames[$row->source_name]['SUMM_AD']+($row->doxod-$row->advSum):($row->doxod-$row->advSum);
			
		}
		
		$curr_type = null;
		$sheet->mergeCells($curr_row, 0, $curr_row, 7);	
		$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'По сети в целом'),$cityFormat);
		$curr_row++;
		foreach($nDs as $key=>$rows){
			foreach($rows as $row){
				if($curr_type != $row->source_type) {
					$curr_type = $row->source_type;
					$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', $row->source_type), $textFormat);
					
					$sheet->write($curr_row, 2,$summTypes[$row->source_type]['SUMM_COST'],$textFormat);
					$sheet->write($curr_row, 3,$summTypes[$row->source_type]['SUMM_CONT'],$textFormat);
					$sheet->write($curr_row, 4,$summTypes[$row->source_type]['SUMM_DMNDS'],$textFormat);
					$sheet->write($curr_row, 5,$summTypes[$row->source_type]['SUMM_DOXOD'],$textFormat);
					$sheet->write($curr_row, 6,$summTypes[$row->source_type]['SUMM_EFF'],$textFormat);
					$sheet->write($curr_row, 7,$summTypes[$row->source_type]['SUMM_AD'],$textFormat);
					
					$curr_row++;
				}
				$sheet->write($curr_row, 1,iconv('UTF-8', 'Windows-1251', $row->source_name),$textFormat);
				$sheet->write($curr_row, 2,$summNames[$row->source_name]['SUMM_COST'],$textFormat);
				$sheet->write($curr_row, 3,$summNames[$row->source_name]['SUMM_CONT'],$textFormat);
				$sheet->write($curr_row, 4,$summNames[$row->source_name]['SUMM_DMNDS'],$textFormat);
				$sheet->write($curr_row, 5,$summNames[$row->source_name]['SUMM_DOXOD'],$textFormat);
				$sheet->write($curr_row, 6,$summNames[$row->source_name]['SUMM_EFF'],$textFormat);
				$sheet->write($curr_row, 7,$summNames[$row->source_name]['SUMM_AD'],$textFormat);
								
				$curr_row++;
			}
		}
		$summ_cost = 0; $summ_cont = 0; $summ_dmnds = 0; $summ_doxod = 0; $summ_eff = 0; $summ_ad = 0;
		foreach($summNames as $key=>$summ){
			$summ_cost += $summNames[$key]['SUMM_COST']; 
			$summ_cont += $summNames[$key]['SUMM_CONT']; 
			$summ_dmnds += $summNames[$key]['SUMM_DMNDS']; 
			$summ_doxod += $summNames[$key]['SUMM_DOXOD']; 
			$summ_eff += $summNames[$key]['SUMM_EFF']; 
			$summ_ad += $summNames[$key]['SUMM_AD'];
		}
		$curr_row++;
		$sheet->write($curr_row, 0,iconv('UTF-8', 'Windows-1251', 'Всего'),$cityAllFormat);
		$sheet->write($curr_row, 2,$summ_cost,$cityAllFormat);
		$sheet->write($curr_row, 3,$summ_cont,$cityAllFormat);
		$sheet->write($curr_row, 4,$summ_dmnds,$cityAllFormat);
		$sheet->write($curr_row, 5,$summ_doxod,$cityAllFormat);
		$sheet->write($curr_row, 6,$summ_eff,$cityAllFormat);
		$sheet->write($curr_row, 7,$summ_ad,$cityAllFormat);
		$xls->close(); 	
	}
}
?>